package com.andong;

import com.microsoft.sqlserver.jdbc.SQLServerException;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;

public class JdbcUtil {
	public static final String TABLE_TITLE = "/* +TABLE+ %d */";
	public static final String VIEW_TITLE = "/* +VIEW+ %d */";
	public static final String TABLE_END = "/* -TABLE_END- */";
	public static final String VIEW_END = "/* -VIEW_END- */";
	private String serverName;
	private int port;
	private String userName;
	private String password;
	private String databaseName;

	public JdbcUtil(String userName, String password, int port, String databaseName) {
		this.userName = userName;
		this.password = password;
		this.port = port;
		this.databaseName = databaseName;
	}

	public JdbcUtil(String serverName, String databaseName, int port, String userName, String password) {
		setServerName(serverName);
		setPort(port);
		this.userName = userName;
		this.password = password;
		setDatabaseName(databaseName);
	}

	public JdbcUtil(String serverName, String databaseName, String userName, String password) {
		this.userName = userName;
		this.password = password;
		setServerName(serverName);
		this.port = 1433;
		setDatabaseName(databaseName);
	}

	public JdbcUtil(String userName, String password) {
		this.userName = userName;
		this.password = password;
		this.port = 1433;
		this.databaseName = "";
		setServerName("localhost");
	}

	public int getPort() {
		return port;
	}

	public void setPort(int port) {
		this.port = port;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDatabaseName() {
		return databaseName;
	}

	public void setDatabaseName(String databaseName) {
		this.databaseName = databaseName;
	}

	public String getServerName() {
		return serverName;
	}

	public void setServerName(String serverName) {
		this.serverName = serverName;
	}

	public void printDBMetaData() {
		Connection conn = null;
		try {
			// 取数据库连接
			conn = getConnection();
			// 获取数据库元数据
			DatabaseMetaData meta = conn.getMetaData();
			System.out.println("JDBC 名称:" + meta.getDriverName());
			System.out.println("JDBC 版本:" + meta.getDriverVersion());
			System.out.println("数据库名称:" + meta.getDatabaseProductName());
			System.out.println("数据库版本:" + meta.getDatabaseProductVersion());
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("数据库连接失败");
		} finally {
			if (conn != null) try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private Connection getConnection() {
		Connection con = null;
		String url = String.format("jdbc:sqlserver://%s:%d;DatabaseName=%s;"
			, this.serverName, this.port, this.databaseName);
		try {
			// 连接数据库
			con = DriverManager.getConnection(url, getUserName(), getPassword());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}

	/**
	 * 取服务器上所有数据库列表
	 *
	 * @return
	 */
	public ArrayList<String> getDataBaseList() {
		Connection conn = null;
		ArrayList<String> dbList = new ArrayList<String>();
		try {
			conn = getConnection();
			// 执行数据库查询语句
			DatabaseMetaData meta = conn.getMetaData();
			ResultSet schrs = meta.getCatalogs();
			while (schrs.next()) {
				dbList.add(schrs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("数据库连接失败");
		} finally {
			if (conn != null) try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return dbList;
	}

	/**
	 * 取数据库中所有表列表, 并转成字符串
	 */
	public String getTableListString() {
		StringBuffer textContent = new StringBuffer();
		TreeMap<String, Integer> tableList = this.getTableList();
		Iterator<Map.Entry<String, Integer>> tableIterator = tableList.entrySet().iterator();
		textContent.append(String.format((TABLE_TITLE + "\n"), tableList.size()));
		while (tableIterator.hasNext()) {
			Map.Entry<String, Integer> entry = tableIterator.next();
			textContent.append(entry.getKey() + "\t" + entry.getValue() + "\n");
		}
		textContent.append(TABLE_END + "\n");
		return textContent.toString();
	}

	/**
	 * 取数据库中所有视图列表, 并转成字符串
	 */
	public String getViewListString() {
		StringBuffer textContent = new StringBuffer();
		TreeMap<String, Integer> viewList = this.getViewList();
		Iterator<Map.Entry<String, Integer>> tableIterator = viewList.entrySet().iterator();
		textContent.append(String.format((VIEW_TITLE + "\n"), viewList.size()));
		while (tableIterator.hasNext()) {
			Map.Entry<String, Integer> entry = tableIterator.next();
			textContent.append(entry.getKey() + "\t" + entry.getValue() + "\n");
		}
		textContent.append(VIEW_END + "\n");
		return textContent.toString();
	}

	/**
	 * 取数据库中所有表列表
	 *
	 * @return
	 */
	public TreeMap<String, Integer> getTableList() {
		Connection conn = null;
		ResultSet rsTable = null;
		Statement statement = null;
		ResultSet rsCount = null;

		TreeMap<String, Integer> tableList = new TreeMap<String, Integer>(((o1, o2) -> {
			if (o1 == null || o2 == null) {
				return 0;
			}
			return o1.toUpperCase().compareTo(o2.toUpperCase());
		}));
		try {
			// 连接数据库
			conn = getConnection();
			// 执行数据库查询语句
			statement = conn.createStatement();
			DatabaseMetaData meta = conn.getMetaData();
			int databaseMajorVersion = meta.getDatabaseMajorVersion();
			if(databaseMajorVersion <= 8){  // 判断SQL版本
				rsTable = statement.executeQuery("SELECT name FROM sysobjects WHERE type = 'U'");
			}
			else if(databaseMajorVersion > 8) {
				rsTable = statement.executeQuery("SELECT name FROM sys.objects WHERE type = 'U'");
			}
			while (rsTable.next()) {
				String sql = String.format("SELECT COUNT(*) FROM [%s]", rsTable.getString("name"));
				Statement countStatement = conn.createStatement();
				rsCount = countStatement.executeQuery(sql);
				rsCount.next();
				tableList.put(rsTable.getString("name"), rsCount.getInt(1));
				rsCount.close();
				countStatement.close();
			}
		} catch (SQLServerException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
//            System.out.println("数据库连接失败");
		} finally {
			if (rsTable != null) try {
				rsTable.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (rsCount != null) try {
				rsCount.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (statement != null) try {
				statement.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (conn != null) try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return tableList;
	}

	/**
	 * 取数据库中所有表列表
	 *
	 * @return
	 */
	public TreeMap<String, Integer> getViewList() {

		Connection conn = null;
		ResultSet rsTable = null;
		Statement statement = null;
		ResultSet rsCount = null;

		TreeMap<String, Integer> viweList = new TreeMap<String, Integer>(((o1, o2) -> {
			if (o1 == null || o2 == null) {
				return 0;
			}
			return o1.toUpperCase().compareTo(o2.toUpperCase());
		}));
		try {
			// 连接数据库
			conn = getConnection();
			// 执行数据库查询语句
			statement = conn.createStatement();
			DatabaseMetaData meta = conn.getMetaData();
			int databaseMajorVersion = meta.getDatabaseMajorVersion();
			if(databaseMajorVersion <= 8) {
				rsTable = statement.executeQuery("SELECT name FROM sysobjects WHERE type = 'V'");
			}
			else if(databaseMajorVersion > 8) {
				rsTable = statement.executeQuery("SELECT name FROM sys.objects WHERE type = 'V'");
			}

			while (rsTable.next()) {
				String sql = String.format("SELECT COUNT(*) FROM [%s]", rsTable.getString("name"));
				Statement countStatement = conn.createStatement();
				rsCount = countStatement.executeQuery(sql);
				rsCount.next();
				viweList.put(rsTable.getString("name"), rsCount.getInt(1));
				rsCount.close();
				countStatement.close();
			}
		} catch (SQLServerException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
//            System.out.println("数据库连接失败");
		} finally {
			if (rsTable != null) try {
				rsTable.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (rsCount != null) try {
				rsCount.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (statement != null) try {
				statement.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (conn != null) try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return viweList;
	}

}